/*
 * Copyright 2009-2019 the original author or authors  
 *
 */
package com.livery.liu.dao.jdbc;

import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;

import com.livery.liu.util.ConnectionUtil;


/**
 * jdbc简单包装类 (不包括批量处理,批量需自己编写)
 *
 * @author  <a href="mailto:livery.liu@gmail.com"> livery liu</a>
 * @param <T>
 */

public class JdbcGenricDaoTemplate{

	/**
	 * 查询单一对象(通过ID或者其它组合条件)
	 * @param sql 按id或者组合条件查询单个对象的sql语句
	 * @param args 传入的参数(可以是数组)
	 * @param rowBeanMapper 属性装配方案的接口
	 * @return 需要的对象
	 * @throws SQLException
	 */
	public <T> T getSinglePo(String sql, Object[] args,IRowBeanMapper<T> rowBeanMapper) throws SQLException{
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = ConnectionUtil.getConnection();
			ps = conn.prepareStatement(sql);
			for (int i = 0; i < args.length; i++){
				ps.setObject(i + 1, args[i]);				
			}
			rs = ps.executeQuery();
			T po = null;
			if (rs.next()) {
				po = rowBeanMapper.mapper(rs);
			}
			return po;
		} catch (SQLException se) {
			throw se;
		} finally {
			ConnectionUtil.free(rs, ps, conn);
		}
	}

	/**
	 * 查询list结果
	 * @param sql 按组合条件查询多个对象的sql语句
	 * @param args 传入的参数(可以是数组)
	 * @param rowBeanMapper 属性装配方案的接口
	 * @return 多个对象的集合
	 * @throws SQLException
	 */
	public <T> List<T> getList(String sql, Object[] args,IRowBeanMapper<T> rowBeanMapper) throws SQLException{
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = ConnectionUtil.getConnection();
			ps = conn.prepareStatement(sql);
			for (int i = 0; i < args.length; i++){
				ps.setObject(i + 1, args[i]);				
			}
			rs = ps.executeQuery();
			List<T> result = new LinkedList<T>();
			if (rs.next()) {
				T po = rowBeanMapper.mapper(rs);
				result.add(po);
			}
			return result;
		} catch (SQLException se) {
			throw se;
		} finally {
			ConnectionUtil.free(rs, ps, conn);
		}		
	}

	/**
	 * 查询单一对象(通过ID或者其它组合条件)<p>
	 * 反射实现装配
	 * @param sql 按id或者组合条件查询单个对象的sql语句
	 * @param args 传入的参数(可以是数组)
	 * @param resultClazz 结果类型实体class(必须是带有属性的class)
	 * @return 
	 * @throws SQLException resultset操作时发生SQl异常
	 * @throws InstantiationException 创建clazz实例时发生异常
	 * @throws IllegalAccessException 创建clazz实例时发生异常
	 * @throws IllegalArgumentException 执行反射方法时发生异常
	 */
	public <T> T getSinglePo(String sql, Object[] args,Class<T> resultClazz) throws SQLException, InstantiationException, IllegalAccessException, InvocationTargetException{
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = ConnectionUtil.getConnection();
			ps = conn.prepareStatement(sql);
			for (int i = 0; i < args.length; i++){
				ps.setObject(i + 1, args[i]);				
			}
			rs = ps.executeQuery();
			T po = null;
			if (rs.next()) {
				po = mapper(rs, resultClazz);			
			}
			return po;
		} catch (SQLException se) {
			throw se;
		} catch (InstantiationException ie) {
			throw ie;
		} catch (IllegalAccessException iae) {
			throw iae;
		} catch (IllegalArgumentException iare) {
			throw iare;
		} catch (InvocationTargetException ite) {
			throw ite;
		} finally {
			ConnectionUtil.free(rs, ps, conn);
		}
	}

	/**
	 * 查询list结果
	 * 反射实现装配
	 * @param sql 按组合条件查询多个对象的sql语句
	 * @param args 传入的参数(可以是数组)
	 * @param resultClazz 结果类型实体class(必须是带有属性的class)
	 * @return
	 * @throws SQLException resultset操作时发生SQl异常
	 * @throws InstantiationException 创建clazz实例时发生异常
	 * @throws IllegalAccessException 创建clazz实例时发生异常
	 * @throws IllegalArgumentException 执行反射方法时发生异常
	 */
	public <T> List<T> getList(String sql, Object[] args,Class<T> resultClazz) throws SQLException, InstantiationException, IllegalAccessException, InvocationTargetException{
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = ConnectionUtil.getConnection();
			ps = conn.prepareStatement(sql);
			for (int i = 0; i < args.length; i++){
				ps.setObject(i + 1, args[i]);				
			}
			rs = ps.executeQuery();
			List<T> result = new LinkedList<T>();	
			if (rs.next()) {
				T po = mapper(rs, resultClazz);
				result.add(po);
			}
			return result;
		} catch (SQLException se) {
			throw se;
		} catch (InstantiationException ie) {
			throw ie;
		} catch (IllegalAccessException iae) {
			throw iae;
		} catch (IllegalArgumentException iare) {
			throw iare;
		} catch (InvocationTargetException ite) {
			throw ite;
		} finally {
			ConnectionUtil.free(rs, ps, conn);
		}		
	}	
	
	/**
	 * 更新操作
	 * @param sql
	 * @param args
	 * @return
	 */
	public int update(String sql, Object[] args) throws SQLException{
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = ConnectionUtil.getConnection();
			ps = conn.prepareStatement(sql);
			for (int i = 0; i < args.length; i++){
				ps.setObject(i + 1, args[i]);				
			}
			return ps.executeUpdate();
		} catch (SQLException e) {
			throw e;
		} finally {
			ConnectionUtil.free(rs, ps, conn);
		}
	}
	
	/**
	 * 插入操作
	 * @param sql
	 * @param args
	 * @return
	 * @throws SQLException
	 */
	public int insert(String sql, Object[] args) throws SQLException{
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = ConnectionUtil.getConnection();
			ps = conn.prepareStatement(sql);
			for (int i = 0; i < args.length; i++){
				ps.setObject(i + 1, args[i]);				
			}
			return ps.executeUpdate();
		} catch (SQLException e) {
			throw e;
		} finally {
			ConnectionUtil.free(rs, ps, conn);
		}
	}
	
	/**
	 * 删除操作
	 * @param sql
	 * @param args
	 * @return
	 * @throws SQLException
	 */
	public int delete(String sql, Object[] args) throws SQLException{
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = ConnectionUtil.getConnection();
			ps = conn.prepareStatement(sql);
			for (int i = 0; i < args.length; i++){
				ps.setObject(i + 1, args[i]);				
			}
			return ps.executeUpdate();
		} catch (SQLException e) {
			throw e;
		} finally {
			ConnectionUtil.free(rs, ps, conn);
		}
	}


	
	/**
	 * 转换rs为clazz的对象，并添加相应的属性字段
	 * @param rs
	 * @param clazz
	 * @return clazz的实例
	 * @throws SQLException resultset操作时发生SQl异常
	 * @throws InstantiationException 创建clazz实例时发生异常
	 * @throws IllegalAccessException 创建clazz实例时发生异常
	 * @throws IllegalArgumentException 执行反射方法时发生异常
	 * @throws InvocationTargetException 执行反射方法时发生异常
	 */
	public <T> T mapper(ResultSet rs,Class<T> clazz) throws SQLException, InstantiationException, IllegalAccessException, IllegalArgumentException, InvocationTargetException {
		String[] columnNames = getColumnNames(rs);
		Method[] ms = clazz.getMethods();	
		T po = clazz.newInstance();
		for (int i = 0; i < columnNames.length; i++) {
			String columnName = columnNames[i];
			String methodName = "set" + columnName;
			for (Method m : ms) {
				if (methodName.equals(m.getName())) {
					m.invoke(po, rs.getObject(columnName));
					break;
				}
			}
		}
		return po;
	}

	/**
	 * 得到ResultSet rs的列名数组
	 * example:<p>
	 * {@literal sql(select userId,name,createTime from user)的ResultSet 返回new Strng[]{userId,name,createTime}}  
	 * @param rs
	 * @return 列名数组
	 * @throws SQLException resultset操作时发生SQl异常
	 */
	private static String[] getColumnNames(ResultSet rs) throws SQLException {
		ResultSetMetaData rsmd = rs.getMetaData();
		int count = rsmd.getColumnCount();
		String[] columnNames = new String[count];
		for (int i = 1; i <= count; i++) {
			columnNames[i - 1] = rsmd.getColumnLabel(i);
		}
		return columnNames;
	}	
}
